Sobre redefinição, natureza e acionadores

Nosso tecnólogo redefine tabelas, compara chaves e alerta sobre DDL em acionadores

Por Tom Kyte

Tenho uma tabela com milhões de registros e preciso atualizá-la periodicamente. Quero executar UPDATE e COMMIT para cada 10.000 registros, por exemplo. Não quero fazer isso em uma única etapa, porque sei que posso acabar tendo problemas de segmento de rollback. Alguma sugestão?

Bem. Essa dúvida é muito mais complicada do que parece. Vamos supor que essa grande transação realmente seja dividida em várias partes pequenas e que, no meio do processo, ocorra algum tipo de erro, como o ORA-01555 (snapshot muito antigo) causado pelo uso freqüente de commit ou por uma falha do sistema. Nessa situação, a atualização em lote está parcialmente executada – "no meio do caminho" – e precisa ser reiniciada. A menos que tenha escrito muito código para torná-la reiniciável, você pode ter uma enorme bagunça para arrumar. Como continuar de onde você parou?

Por exemplo, o problema com o código com estas características  




declare
	cursor c is select * from t;
begin
	open c;
	loop
		fetch c bulk collect 
		into l_data limit 500;
		... some process ...
		forall I in 1 .. l_data.count
		        update t set ... 
		/* using l_data */
		commit;
		exit when c%notfound;
	end loop;
	close c;
end;

é que são muito grandes as chances de ocorrer um erro ORA-01555, porque você está lendo a tabela que está modificando, e SELECT * FROM T precisa começar no momento em que a consulta começou. Como você está modificando a tabela, aumenta a probabilidade de ocorrer um erro ORA-01555 – e foi você quem o causou. O undo gerado com o UPDATE da tabela T provavelmente será necessário para SELECT na tabela T; entretanto, ao executar COMMIT, você permite que o banco de dados reutilize o undo gerado – e se ele reutilizá-lo (porque a retenção de undo está definida como muito pequena ou porque não há espaço de undo suficiente alocado para reter toda a operação de undo que você está gerando), você quase certamente obterá o erro ORA-01555.

Além disso, quando é gerado o erro ORA-01555 e o bloco do código falha, como você o reinicia? Talvez seja necessária uma coluna nessa tabela que consiga informar se ela já foi atualizada em massa ou outra tabela de rastreamento na qual você inseriu as chaves primárias das linhas já modificadas, usando "SELECT * FROM T WHERE PK NOT IN (select pk from tracking_table)" ou uma abordagem semelhante para fazer isso.

Então, você se depara com a tarefa de escrever um monte de código para realizar essa tarefa. Eu prefiro seguir um destes procedimentos:

  • Simplesmente atualizar a tabela em uma única instrução. Esta abordagem será de longe a mais eficiente em termos de utilização de recursos. A preocupação com possíveis problemas de segmento de rollback é em grande medida compensada pelo fato de que o undo é necessário para que a consulta seja concluída com sucesso.
  • Usar DBMS_REDEFINITION para processar a atualização. Este procedimento terá a possível vantagem de ser uma operação online (sem conflitos de bloqueio – o que pode ser bom ou ruim, dependendo das circunstâncias). Também evitará qualquer migração de linha que possa acontecer com um UPDATE que torne as linhas "maiores" do que eram. Além disso, resultará nas estruturas de dados mais compactas depois. Você pode inclusive adicionar um ORDER BY à redefinição da tabela no Banco de Dados Oracle 10g e superior para reseqüenciar as linhas em disco se isso fizer sentido.
  • Usar CREATE TABLE AS SELECT para selecionar "updated data" em uma nova tabela. Isso é semelhante à opção DBMS_REDEFINITION, mas é uma operação offline (por isso nenhuma modificação poderá ser permitida na tabela original enquanto você estiver fazendo isso) e é mais manual. Enquanto a opção DBMS_REDEFINITION automatiza a criação de índices, concessões e restrições – tudo o que é necessário para criar uma cópia de um objeto – o método CREATE TABLE AS SELECT exige que você faça tudo manualmente.

Vamos analisar a utilização do DBMS_REDEFINITION para criar uma cópia atualizada de uma tabela. A meta é criar uma nova coluna que consiste em uma concatenação de três colunas existentes, eliminar da tabela as três colunas existentes e seqüenciar as linhas em disco por outra coluna (para que os dados existentes sejam classificados por essa coluna em disco). Vou começar com uma cópia de ALL_OBJECTS para teste, conforme mostra a Listagem 1.

Listagem 1 do código: Tabela T inicial  




SQL> create table t
  2    as
  3    select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  4         OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE,
  5         CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
  6         TEMPORARY, GENERATED, SECONDARY
  7      from all_objects
  8    order by dbms_random.random;
Table created.

SQL> alter table t
  2    add constraint t_pk
  3    primary key(object_id);
Table altered.

SQL> select object_name
  2    from t
  3    where rownum <= 5    
4    /  

OBJECT_NAME 
-------------------- 
java/rmi/MarshalException  
SYS_C008650 
ALL_IDENTIFIERS 
/5ed18cf1_SimpleAuthPopupBasic 
/1cca9769_MonitoredObjectImpl

Então, tenho uma tabela T com uma restrição (e possivelmente outras, como concessões, índices, acionadores e assim por diante). Como pode ser visto na Listagem 1, os dados são armazenados aleatoriamente em disco – com certeza não são classificados por OBJECT_NAME, como mostra a verificação de tabela completa que comecei com SELECT * FROM T. Quero pegar três colunas – TEMPORARY, GENERATED e SECONDARY – e concatená-las em uma nova coluna FLAGS. Além disso, gostaria de “tirar” as colunas TEMPORARY, GENERATED e SECONDARY, bem como as colunas SUBOBJECT_NAME, DATA_OBJECT_ID e TIMESTAMP da nova tabela e, por fim, organizar os dados existentes por OBJECT_NAME. Observe que quaisquer dados recém-adicionados não serão armazenados na ordem classificada na tabela.

Para fazer isso, precisarei de uma tabela provisória para copiar os dados existentes nela:  




SQL> create table t_interim
  2   (
  3     object_id          number,
  4     object_type       varchar2(18),
  5     owner               varchar2(30),
  6     object_name      varchar2(30),
  7     created             date,
  8     last_ddl_time     date,
  9     status               varchar2(7),
 10     flags                 varchar2(5)
 11   )
 12   /
Table created.

Agora estou pronto para começar o UPDATE, usando o pacote DBMS_REDEFINITION – disponível com o Banco de Dados Oracle9i e superior – para fazer uma redefinição de tabela online, conforme mostra a Listagem 2. (O recurso de classificação de dados durante uma redefinição foi adicionado no Banco de Dados Oracle 10g Release 1.)

Listagem 2 do código: Procedimento DBMS_REDEFINITION.START_REDEF_TABLE 




SQL> declare
  2      l_colmap varchar(512);
  3    begin
  4      l_colmap :=
  5           'object_id,
  6            object_type,
  7            owner,
  8            object_name ,
  9            created,
 10            last_ddl_time,
 11            status,
 12            temporary || ''/'' ||
 13            generated || ''/'' ||
 14            secondary flags ';
 15
 16      dbms_redefinition.start_redef_table
 17      (  uname           => user,
 18         orig_table      => 'T',
 19         int_table       => 'T_INTERIM',
 20         orderby_cols   => 'OBJECT_NAME',
 21         col_mapping   => l_colmap );
 22   end;
 23   /
PL/SQL procedure successfully completed.

O parâmetro COL_MAPPING no procedimento START_REDEF_TABLE é o que faz a mágica das colunas UPDATE e DROP. Basicamente você executa um SELECT nos dados, usando o parâmetro COL_MAPPING, que pode incluir funções (que você teria usado na cláusula SET de UPDATE). O parâmetro ORDERBY_COLS executa o reseqüenciamento dos dados da tabela existentes em disco. Se você habilitar SQL_TRACE=TRUE ao executar a chamada de START_REDEF_TABLE, verá um comando INSERT como este sendo executado : 




INSERT 
/*+ BYPASS_RECURSIVE_CHECK APPEND  */ 
INTO "OPS$TKYTE"."T_INTERIM"
("OBJECT_ID","OBJECT_TYPE","OWNER",
"OBJECT_NAME", "CREATED",
"LAST_DDL_TIME","STATUS","FLAGS")
SELECT "T"."OBJECT_ID",
"T"."OBJECT_TYPE",
"T"."OWNER","T"."OBJECT_NAME",
"T"."CREATED","T"."LAST_DDL_TIME",
"T"."STATUS",
"T"."TEMPORARY"||'/'||
"T"."GENERATED"||'/'||
"T"."SECONDARY" 
FROM "OPS$TKYTE"."T" "T" 
ORDER BY OBJECT_NAME

Observe que as colunas TEMPORARY, GENERATED, SECONDARY, SUBOBJECT_NAME, DATA_OBJECT_ID e TIMESTAMP não são copiadas para a tabela T_INTERIM, mas as colunas TEMPORARY, GENERATED e SECONDARY são concatenadas na nova coluna FLAGS.

Como esse INSERT inclui APPEND na dica, você pode não apenas ignorar UNDO (o que ocorre como padrão com APPEND – nenhum undo é gerado para a tabela T_INTERIM durante esse carregamento inicial), como também pode, se preferir, ignorar a geração de REDO para essa tabela alterando-a para que seja NOLOGGING antes de executar o procedimento START_REDEF_TABLE. (Se você ignorou a geração de REDO, antes de fazer isso consulte o grupo responsável por backups! Esse grupo precisará programar um backup do arquivo de dados em questão imediatamente após essa operação, para tornar os novos dados recuperáveis).

Esse procedimento START_REDEF_TABLE que acabei de chamar fez algumas coisas. Ele copiou os dados de T para T_INTERIM, copiou somente os dados de interesse, classificou os dados durante o carregamento e fez tudo com absoluta eficiência (ignorando UNDO e opcionalmente REDO). Também configurou replicação suficiente entre T e T_INTERIM para permitir que eu os mantivesse em sincronia, para que, ao final da redefinição, as duas tabelas fossem logicamente equivalentes – elas têm a mesma quantidade de linhas.

Agora preciso copiar as "coisas" dependentes – como índices, restrições e concessões. Posso usar a chamada API de COPY_TABLE_DEPENDENTS (um recurso do Banco de Dados Oracle 10g e superior) para fazer isso ou copiar os dependentes da tabela eu mesmo, usando DDL (junto com qualquer opção que quiser: NOLOGGING, PARALLEL e assim por diante). Neste exemplo, uso a chamada API de COPY_TABLE_DEPENDENTS: 




SQL> variable nerrors number

SQL> begin
  2    dbms_redefinition.copy_
table_dependents
  3    ( user, 'T', 'T_INTERIM',
  4      copy_indexes => dbms_
redefinition.cons_orig_params,
  5      num_errors => :nerrors );
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> print nerrors

   NERRORS
------
             0

Pronto! Se você fosse consultar o dicionário de dados agora, veria duas tabelas com restrições, concessões, acionadores e índices equivalentes e assim por diante. Estou pronto para concluir a redefinição agora – ela envolverá a sincronização das tabelas e o bloqueio de T e T_INTERIM por um breve momento para trocar seus nomes de objeto no dicionário de dados, para que T se torne T_INTERIM e T_INTERIM se torne T: 




SQL> begin
  2        dbms_redefinition.finish_
redef_table
  3       ( user, 'T', 'T_INTERIM' );
  4    end;
  5    /
PL/SQL procedure successfully completed.

SQL> select object_name, flags
  2    from t
   3   where rownum <= 5;  
OBJECT_NAME                    FLAGS 
-----------------              ---  
/1000323d_DelegateInvocationHa N/N/N  
/1000323d_DelegateInvocationHa N/N/N 
/1000e8d1_LinkedHashMapValueIt N/N/N 
/1000e8d1_LinkedHashMapValueIt N/N/N  
/1005bd30_LnkdConstant         N/N/N

Como pode ser visto, a tabela T agora é a tabela atualizada, com a nova coluna FLAGS e as linhas existentes na tabela classificada em disco por OBJECT_NAME.

Conforme mencionado anteriormente, o uso de CREATE TABLE AS SELECT para selecionar os dados em uma nova tabela poderia gerar um resultado semelhante ao uso de DBMS_REDEFINITION, mas o processo, incluindo a criação de índices, concessões e restrições, seria mais manual. Entretanto, na edição padrão do Banco de Dados Oracle, em que a redefinição online não está disponível, o uso de CREATE TABLE AS SELECT pode ser a maneira mais eficiente de fazer isso.

Chaves naturais ou substitutas

O projeto no qual estou trabalhando tem a regra de design de banco de dados de que todas as tabelas precisam ter uma chave substituta. Isso é obrigatório mesmo se há uma chave natural perfeitamente boa.

A principal justificativa parece ser a melhoria da eficiência de junção, ao eliminar a possibilidade de ter de juntar duas tabelas em mais de uma coluna.

Pessoalmente, não sou favorável a chaves substitutas em geral ou a esse tipo de política genérica em especial. Acredito que normalmente há muito o que ganhar quando a tabela filho herda a chave primária da tabela pai como parte da chave primária da tabela filho.

Você tem algum comentário sobre chaves naturais e chaves substitutas em geral? Você é a favor das chaves substitutas no lugar das naturais? Quão rigorosas precisariam ser as considerações de performance para justificar esse esquema?

Ah, sim. O velho debate de sempre – aquele em que nenhum lado nunca vai "ganhar". Ter uma regra como essa é o exemplo perfeito do motivo pelo qual na verdade não gosto do termo "melhores práticas" ou "regras práticas". As melhores práticas para um são um pesadelo para o outro.

Se você tem uma chave natural, não tenha dúvida – use-a. A chave natural deve ser imutável e sensata; às vezes ela é rara na vida real, mas existe.

Por exemplo, se eu tivesse uma tabela DOCUMENT e uma outra DOCUMENT_VERSION, com certeza eu usaria document_id (que poderia ser uma chave substituta) como chave primária de uma tabela e a expressão combinada document_id,version# como chave principal da outra tabela (e DOCUMENT_VERSION pode ter uma associação com autores, por isso sua chave primária é uma chave estrangeira em outras partes do sistema também).

Ou seja, eu a configuraria da seguinte forma: 




create table document 
( document_id number primary key, 
  -- populated by a sequence perhaps
  other_data...
);

create table document_version
( document_id references document,
  version# number,
  other_data ...
  constraint doc_ver_pk primary key
  (document_id,version#)
);

pelos motivos que você acabou de citar. Entretanto, deve-se ter a certeza de que a chave primária seja imutável. Sempre. Nesse caso, a chave substituta na tabela DOCUMENT é imutável e a chave natural (que por acaso inclui uma chave substituta de outro lugar) na tabela DOCUMENT_VERSION também.

A chave natural teria de estar presente em minha tabela desde o início, com restrições NOT NULL e UNIQUE nela. O uso de uma chave substituta aqui somente aumentaria o trabalho de todas as operações INSERT – que agora teriam de gerar uma chave substituta, além de restringir exclusivamente tanto a chave substituta quanto a natural. Por isso, se a chave natural é imutável e razoável, não tenha dúvida – use-a. (Nesse caso, razoável quer dizer, por exemplo, que ela não utiliza 17 colunas para armazenar a chave natural – ter 2, 3, 4 ou talvez até 5 colunas é razoável.) Para ler as muitas e diversificadas discussões sobre esse assunto polêmico, consulte asktom.oracle.com/pls/ask/search?p_string=%22natural+key%22.

DDL em acionadores

Tudo o que li até hoje sobre acionadores afirma explicitamente que DDL, devido a um COMMIT implícito, não pode ser usado em um acionador. Entretanto, vejo em blogs e outros espaços o pessoal afirmando que fez o DLL funcionar através de vários ajustes, mas eu nunca consegui fazer isso. Tenho duas perguntas em relação a isso:

  1. Por que o uso do pragma autonomous_transaction não resolve essa questão em um procedimento chamado de um acionador?
  2. Você conhece alguma solução alternativa para executar DDL em um procedimento chamado de um acionador?

Bem, respondendo à primeira pergunta, o pragma autonomous_transaction até deixaria você executar DDL em um acionador, mas ainda bem que você nunca conseguiu. Fique muito feliz por não ter conseguido. Em resposta à segunda pergunta, no caso extremamente raro – raro mesmo – em que isso é realmente desejável e necessário, sugiro usar DBMS_JOB para programar a instrução CREATE depois de a transação ser concluída.

Primeiro, pense por um momento nos desdobramentos de se fazer um trabalho não-transacional em um acionador. O que acontece quando é necessário fazer rollback? Bom. É evidente que o DDL não faria rollback – você seria deixado "no meio do caminho". O DDL teria ocorrido, mas a transação que causou o DDL não teria ocorrido (devido ao rollback do DLL). A bagunça seria geral.

Sempre que você for tentado a fazer algo não-transacional em um acionador, pense 500 vezes antes e decida sempre não fazê-lo. Ele pode levar a verdadeiras catástrofes.

Se você usar DBMS_JOB, as características serão mais ou menos estas: 




SQL> create table do_ddl
   2    ( job number primary key,
   3      stmt varchar2(4000)
   4    );
Table created.

SQL> create or replace
   2    procedure do_ddl_safely 
       (  p_job in number )
   3    is
   4        l_rec do_ddl%rowtype;
   5    begin
   6       select *
   7         into l_rec
   8         from do_ddl
   9        where job = p_job;
 10      execute immediate l_rec.stmt;
 11   end;
 12   /
Procedure created.

E então você usará um bloco de código semelhante ao seguinte para chamar o procedimento de DDL imediatamente após concluir a transação: 



SQL> declare
  2        l_job number;
  3    begin
  4        dbms_job.submit
  5        ( l_job, 
              'do_ddl_safely(JOB);' );
  6         insert into do_ddl
  7         ( job, stmt ) values
  8         ( l_job, '...The statement 
              to be executed...' );
  9    end;
 10     /

DO_DDL_SAFELY é um procedimento armazenado que você escreve que executa DDL, detectando erros se necessário, notificando as pessoas e fazendo corretamente o que é preciso. Ele será executado imediatamente após o commit.

E o melhor de tudo é que, se você fizer rollback, o INSERT na fila de trabalho também o fará. Você está protegido – o DDL não ocorrerá. Use esta abordagem sempre que pensar em fazer algo não-transacional em um acionador.

Veja esta série sobre um tópico relacionado (consistência de gravação), incluindo como os acionadores podem disparar mais de uma vez para uma determinada instrução – outro motivo para evitar operações não-transacionais em um acionador:

Tom Kyte é um catequizador que trabalha com banco de dados na divisão de Tecnologia de Servidores da Oracle, na empresa desde 1993. É autor de Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005) e Effective Oracle by Design (Oracle Press, 2003), dentre outros.